#!/usr/bin/env python
# -*- encoding: utf-8 -*-
'''
@Time    :   2021/08/25 21:33:11
@Author  :   Leo Wood 
@Contact :   leowood@foxmail.com
'''
import os
import sys
os.chdir(sys.path[0])
from re import S
from bs4 import BeautifulSoup
from pyMySql import pySql
import json
import re
from tqdm import tqdm


with open('db.json', 'r', encoding='utf-8') as f:
    db_info = json.load(f)
db_info = db_info['Medical_Knowledge']
db_server = pySql(ip=db_info['ip'], user=db_info['user'], pwd=db_info['pwd'], db=db_info['db'])


id = 1000

def get_similar_words_from_html(word,path,source,cmesh_id,category_id):

    word_dict = {"similar_words":[],"definition":'','description':''}

    with open(path,'r',encoding='utf-8') as f:
        soup = BeautifulSoup(f.read(), "html.parser")

        description_soup = soup.find('div',class_='alert-primary')
        if description_soup:
            description = description_soup.get_text().strip()
            word_dict['description'] = description

        definition_soup = soup.find(text=re.compile(r'.*基本释义.*'))
        if definition_soup:
            definition = ''
            if '[' in definition_soup.parent.parent.next_sibling.next_sibling.string and '[英' not in definition_soup.parent.parent.next_sibling.next_sibling.string:
                definition_tag = definition_soup.parent.parent.next_sibling.next_sibling.next_sibling.next_sibling
                definition = definition_tag.string.strip()
            else:
                definition_tag = definition_soup.parent.parent.next_sibling.next_sibling
                definition = definition_tag.string.strip()
            while True:
                if definition_tag.next_sibling.next_sibling.string:
                    definition += definition_tag.next_sibling.next_sibling.string.strip()
                    definition_tag = definition_tag.next_sibling.next_sibling
                else:
                    break

            word_dict['definition'] = definition

        similar_words_soup = soup.find(text=re.compile(r'.*的相似词.*'))

        if similar_words_soup:
            similar_words_soup = similar_words_soup.parent.parent.next_sibling.next_sibling
            for child in similar_words_soup.children:
                if ' ' in child.string:
                    for w in child.string.split():
                        word_dict['similar_words'].append(w)
                elif child.string.strip():
                     word_dict['similar_words'].append(child.string.strip())

    global id
    id += 1
    sql = ''
    if source == 0: ##不在当前表格中
        sql = "insert into Term_info(termId,termText,termSimilarWords,termDescripton,termDefinition) values({},'{}','{}','{}','{}')".format(id,word.replace("'","''"),';;'.join(word_dict['similar_words']).replace("'","''"),word_dict['description'].replace("'","''"),word_dict['definition'].replace("'","''"))
    if source == 1: ##在cmesh表格中
        sql = "insert into Term_info(termId,cmeshId,termText,termSimilarWords,termDescripton,termDefinition) values({},{},'{}','{}','{}','{}')".format(id,cmesh_id,word.replace("'","''"),';;'.join(word_dict['similar_words']).replace("'","''"),word_dict['description'].replace("'","''"),word_dict['definition'].replace("'","''"))
    if source == 2: ##在category表格中
        sql = "insert into Term_info(termId,categoryId,termText,termSimilarWords,termDescripton,termDefinition) values({},{},'{}','{}','{}','{}')".format(id,category_id,word.replace("'","''"),';;'.join(word_dict['similar_words']).replace("'","''"),word_dict['description'].replace("'","''"),word_dict['definition'].replace("'","''"))
    
    if sql:
        db_server.write_sql(sql)
        


if __name__ == '__main__':

    ## 获取文件列表
    files = {}
    for root,dir,filenames in os.walk(r'F:\LiuHuan\Projects\Spiders\KMcha\MESH\html'):
        for name in filenames:
            word = name.split('.')[0]
            files[word] =  os.path.join(root,name)
    for root,dir,filenames in os.walk(r'F:\LiuHuan\Projects\Spiders\KMcha\sougou\html'):
        for name in filenames:
            word = name.split('.')[0]
            files[word] =  os.path.join(root,name)
    for root,dir,filenames in os.walk(r'F:\LiuHuan\Projects\Spiders\KMcha\MedKG\html'):
        for name in filenames:
            word = name.split('.')[0]
            files[word] =  os.path.join(root,name)
    for root,dir,filenames in os.walk(r'F:\LiuHuan\Projects\Spiders\KMcha\THU\html'):
        for name in filenames:
            word = name.split('.')[0]
            files[word] =  os.path.join(root,name)
    print(len(files.keys()))

    not_in_words = []

    ## cmesh 词表
    print("CmSH:")
    sql = "select termText,termId from CMeSH_terms"
    results = db_server.read_sql(sql)

    cmesh_word_list = [result[0] for result in results]

    for result in tqdm(results):
        word = result[0]
        word_id = result[1]
        if word in files.keys():
            path = files[word]
            source = 1
            cmesh_id = word_id
            category_id = 0
            try:
                get_similar_words_from_html(word,path,source,cmesh_id,category_id)
            except Exception as e:
                print(e)
                print(path)
                exit()
        else:
            not_in_words.append(word)
    
    ## category 词表
    print("Category:")
    sql = "select termText,termId from Categorized_terms"
    results = db_server.read_sql(sql)

    category_word_list = [result[0] for result in results]

    for result in tqdm(results):
        word = result[0]
        word_id = result[1]
        if word in files.keys():
            path = files[word]
            source = 2
            category_id = word_id
            cmesh_id = 0
            try:
                get_similar_words_from_html(word,path,source,cmesh_id,category_id)
            except Exception as e:
                print(e)
                print(path)
                exit()
        else:
            not_in_words.append(word)

    ## 两个词表都不在的词
    for word in tqdm(files.keys()):
        if word not in cmesh_word_list:
            if word not in category_word_list:
                path = files[word]
                source = 0
                category_id = 0
                cmesh_id = 0
                try:
                    get_similar_words_from_html(word,path,source,cmesh_id,category_id)
                except Exception as e:
                    print(e)
                    print(path)
                    exit()
    ## 缺失的词
    with open(r'F:\LiuHuan\Projects\Spiders\KMcha\missed_word_list.txt','w',encoding='utf-8') as f:
        for word in not_in_words:
            f.write(word + '\n')


    db_server.close()



